package pe.com.cineplanet.dao.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import pe.com.cineplanet.bean.cineplanet.Cartelera;
import pe.com.cineplanet.bean.cineplanet.CarteleraId;
import pe.com.cineplanet.bean.cineplanet.Cine;
import pe.com.cineplanet.bean.cineplanet.Constantes;
import pe.com.cineplanet.bean.cineplanet.Horario;
import pe.com.cineplanet.bean.cineplanet.Pelicula;
import pe.com.cineplanet.dao.dao.CarteleraDAO;


public class CarteleraDAOImpl implements CarteleraDAO{

	protected JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	
	

	@SuppressWarnings("unchecked")
	public List<Cartelera> findByCine(String cineId) {
		System.out.println("Buscando en el Cine: " + cineId);
		System.out.println("----------------------------------");
		StringBuffer sql =  new StringBuffer();
		sql.append("SELECT ");
		sql.append(" p.id, p.titulo, ci.id, ci.nombre,  ");
		sql.append(" p.tipo_pelicula, p.tipo_publico, p.tipo_emision, p.pais, ");
		sql.append(" group_concat(h.descripcion) ");
		sql.append("FROM t_cartelera c ");
		sql.append("inner join t_cine ci on c.id_cine = ci.id ");
		sql.append("inner join t_pelicula p on c.id_pelicula = p.id ");
		sql.append("inner join t_horario h on c.id_horario = h.id ");
		sql.append("where ci.id = ? ");
		sql.append("group by p.id, ci.id ");
		sql.append("order by p.id  ");
		
		Object[] params = new Object[]{ cineId };
		
		List<Cartelera> list = jdbcTemplate.query(sql.toString(), params, new RowMapper(){

			public Cartelera mapRow(ResultSet rs, int arg1) throws SQLException {
				Pelicula p = new Pelicula(rs.getString(1), rs.getString(2));
				String horario = rs.getString(9);
				horario = Constantes.formatearHorario(horario);
				CarteleraId id = new CarteleraId(horario, p);
				Cartelera c = new Cartelera(id, "");
				Cine ci = new Cine(rs.getString(3), rs.getString(4));
				c.setCine(ci);
				p.setTipoPelicula(rs.getString(5));
				p.setPublico(rs.getString(6));
				p.setEmision(rs.getString(7));
				p.setPais(rs.getString(8));
				return c;
				
			}
			
		});
		
		if(list==null) System.out.println("No se encuentra la pelicula");
		else System.out.println("Se encontraron (" + list.size() + ") registros ");
		
		System.out.println();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Cartelera> findByPelicula(String peliculaId){
		System.out.println("Buscando pelicula " + peliculaId );
		System.out.println("----------------------------------");
		
		StringBuffer sql =  new StringBuffer();
		sql.append("SELECT ");
		sql.append(" p.id, p.titulo, ci.id, ci.nombre,  ");
		sql.append(" p.tipo_pelicula, p.tipo_publico, p.tipo_emision, p.pais, ");
		sql.append(" group_concat(h.descripcion) ");
		sql.append("FROM t_cartelera c ");
		sql.append("inner join t_cine ci on c.id_cine = ci.id ");
		sql.append("inner join t_pelicula p on c.id_pelicula = p.id ");
		sql.append("inner join t_horario h on c.id_horario = h.id ");
		sql.append("where p.id = ?");
		sql.append("group by p.id, ci.id ");
		sql.append("order by p.id  ");
		
		Object[] params = new Object[]{ peliculaId };
		
		List<Cartelera> list = jdbcTemplate.query(sql.toString(), params, new RowMapper(){

			public Cartelera mapRow(ResultSet rs, int arg1) throws SQLException {
				Pelicula p = new Pelicula(rs.getString(1), rs.getString(2));
				String horario = rs.getString(9);
				horario = Constantes.formatearHorario(horario);
				CarteleraId id = new CarteleraId(horario, p);
				Cartelera c = new Cartelera(id, "");
				Cine ci = new Cine(rs.getString(3), rs.getString(4));
				c.setCine(ci);
				p.setTipoPelicula(rs.getString(5));
				p.setPublico(rs.getString(6));
				p.setEmision(rs.getString(7));
				p.setPais(rs.getString(8));
				return c;
				
			}
			
		});
		
		if(list==null) System.out.println("No se encuentra la pelicula");
		else System.out.println("Se encontraron (" + list.size() + ") registros ");
		System.out.println();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Cartelera> findByHorario(Horario horario) {
		System.out.println("Buscando peliculas en el horario: " + horario.toString());
		System.out.println("----------------------------------");
		
		StringBuffer sql =  new StringBuffer();
		sql.append("SELECT ");
		sql.append("h.descripcion, p.id, p.titulo, s.nombre, ci.id, ci.nombre, ci.direccion, s.nombre  ");
		sql.append("FROM t_cartelera c ");
		sql.append("inner join t_cine ci on c.id_cine = ci.id ");
		sql.append("inner join t_pelicula p on c.id_pelicula = p.id ");
		sql.append("inner join t_horario h on c.id_horario = h.id ");
		sql.append("inner join t_sala s on c.id_sala = s.id ");
		sql.append("where h.id = ?");
		
		Object[] params = new Object[]{ horario.getValue() };
		
		List<Cartelera> list = jdbcTemplate.query(sql.toString(), params, new RowMapper(){

			public Cartelera mapRow(ResultSet rs, int arg1) throws SQLException {
				Pelicula p = new Pelicula(rs.getString(2), rs.getString(3));
				CarteleraId id = new CarteleraId(rs.getString(1), p);
				Cartelera c = new Cartelera(id, rs.getString(8));
				Cine ci = new Cine(rs.getString(5), rs.getString(6));
				c.setCine(ci);
				System.out.println(c.toString());
				return c;
				
			}
			
		});
		
		if(list==null) System.out.println("No se encuentra la pelicula");
		else System.out.println("Se encontraron (" + list.size() + ") registros ");
		System.out.println();
		return list;
	}



	@SuppressWarnings("unchecked")
	@Override
	public List<Pelicula> findPeliculasCartelera() {
		System.out.println("Buscando peliculas ");
		System.out.println("----------------------------------");
		
		StringBuffer sql =  new StringBuffer();
		sql.append("SELECT ");
		sql.append("p.id, p.titulo  ");
		sql.append("FROM t_cartelera c ");
		sql.append("inner join t_pelicula p on c.id_pelicula = p.id ");
		sql.append("where c.estado = 1 group by p.id "); // Estado 1 indica cartelera activa.

		List<Pelicula> list = jdbcTemplate.query(sql.toString(), new RowMapper(){

			public Pelicula mapRow(ResultSet rs, int arg1) throws SQLException {
				return new Pelicula(rs.getString(1), rs.getString(2));
				
			}
			
		});
		
		if(list==null) System.out.println("No se encuentra la pelicula");
		else System.out.println("Se encontraron (" + list.size() + ") registros ");
		System.out.println();
		return list;
	}

	

}
